package com.example.teacherservice.repository;

import com.example.teacherservice.entity.StuCouProgress;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

// StuCouProgressRepository.java
public interface StuCouProgressRepository extends JpaRepository<StuCouProgress, Long> {

    Optional<StuCouProgress> findByStuIdAndCouIdAndChaId(Long stuId, Long couId, Long chaId);

    @Query("SELECT p FROM StuCouProgress p WHERE p.stuId = :studentId AND p.couId = :courseId")
    List<StuCouProgress> findByStudentAndCourse(@Param("studentId") Long studentId,
                                                @Param("courseId") Long courseId);

    @Modifying
    @Query("UPDATE StuCouProgress p SET p.learnedStatus = 1 WHERE p.stuId = :studentId AND p.couId = :courseId AND p.chaId = :chapterId")
    void markAsCompleted(@Param("studentId") Long studentId,
                         @Param("courseId") Long courseId,
                         @Param("chapterId") Long chapterId);


    // 更新学习进度
    @Modifying
    @Transactional
    @Query("UPDATE StuCouProgress p SET " +
            "p.learnedDuration = :learnedDuration, " +
            "p.mediaProgress = :mediaProgress, " +
            "p.mediaDuration = :mediaDuration, " +
            "p.learnedStatus = :learnedStatus, " +
            "p.positioning = :positioning " +
            "WHERE p.stuId = :stuId AND p.couId = :couId AND p.chaId = :chaId")
    void updateProgress(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId,
            @Param("chaId") Long chaId,
            @Param("learnedDuration") Long learnedDuration,
            @Param("mediaProgress") Long mediaProgress,
            @Param("mediaDuration") Long mediaDuration,
            @Param("learnedStatus") Integer learnedStatus,
            @Param("positioning") Long positioning);


    // 获取课程的整体进度
    @Query("SELECT AVG(p.learnedDuration/p.mediaDuration) FROM StuCouProgress p " +
            "WHERE p.stuId = :stuId AND p.couId = :couId")
    Double getCourseProgressPercentage(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId);

    // 获取学生所有课程的进度
    @Query("SELECT p.couId, AVG(p.learnedDuration/p.mediaDuration) " +
            "FROM StuCouProgress p WHERE p.stuId = :stuId GROUP BY p.couId")
    List<Object[]> getAllCoursesProgress(@Param("stuId") Long stuId);


    // 2. 批量查询多个章节的学习进度
    @Query("SELECT p FROM StuCouProgress p " +
            "WHERE p.stuId = :stuId AND p.couId = :couId AND p.chaId IN :chaIds")
    List<StuCouProgress> findProgressForChapters(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId,
            @Param("chaIds") List<Long> chaIds);

    // 3. 查询课程所有章节的学习进度
    @Query("SELECT p FROM StuCouProgress p " +
            "WHERE p.stuId = :stuId AND p.couId = :couId")
    List<StuCouProgress> findProgressForCourse(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId);

    // 4. 计算课程整体进度
    @Query("SELECT AVG(p.learnedDuration/p.mediaDuration) " +
            "FROM StuCouProgress p " +
            "WHERE p.stuId = :stuId AND p.couId = :couId")
    Double calculateCourseProgressPercentage(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId);

    // 5. 标记章节为已完成
    @Modifying
    @Transactional
    @Query("UPDATE StuCouProgress p SET " +
            "p.learnedStatus = 1, " +
            "p.learnedDuration = p.mediaDuration, " +
            "p.mediaProgress = p.mediaDuration " +
            "WHERE p.stuId = :stuId AND p.couId = :couId AND p.chaId = :chaId")
    void markChapterAsCompleted(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId,
            @Param("chaId") Long chaId);

    // 6. 更新学习进度
    @Modifying
    @Transactional
    @Query("UPDATE StuCouProgress p SET " +
            "p.mediaProgress = :progress, " +
            "p.learnedDuration = :duration, " +
            "p.positioning = :position " +
            "WHERE p.stuId = :stuId AND p.couId = :couId AND p.chaId = :chaId")
    void updateLearningProgress(
            @Param("stuId") Long stuId,
            @Param("couId") Long couId,
            @Param("chaId") Long chaId,
            @Param("progress") Long progress,
            @Param("duration") Long duration,
            @Param("position") Long position);

    // 7. 批量获取学生的学习进度
    @Query("SELECT p FROM StuCouProgress p " +
            "WHERE p.stuId = :stuId AND p.couId IN :couIds")
    List<StuCouProgress> findProgressForCourses(
            @Param("stuId") Long stuId,
            @Param("couIds") List<Long> couIds);
}
